//
//  DataBase.m
//  AI
//
//  Created by zjhc on 2018/2/12.
//  Copyright © 2018年 zjhc. All rights reserved.
//  Description:这里主要通过FMDB封装了对AudioFile文件名称的增删改查操作

#import "DataBase.h"
#import <FMDB.h>
#import "AudioFileModel.h"

static DataBase *_DBCtl = nil;

@interface DataBase()<NSCopying,NSMutableCopying>{
    FMDatabase  *_db;
    
}

@end

@implementation DataBase
+(instancetype)sharedDataBase{
    
    if (_DBCtl == nil) {
        
        _DBCtl = [[DataBase alloc] init];
        
        [_DBCtl initDataBase];
        
    }
    
    return _DBCtl;
    
}

+(instancetype)allocWithZone:(struct _NSZone *)zone{
    
    if (_DBCtl == nil) {
        
        _DBCtl = [super allocWithZone:zone];
        
    }
    
    return _DBCtl;
    
}

-(id)copy{
    
    return self;
    
}

-(id)mutableCopy{
    
    return self;
    
}

-(id)copyWithZone:(NSZone *)zone{
    
    return self;
    
}

-(id)mutableCopyWithZone:(NSZone *)zone{
    
    return self;
    
}

-(void)initDataBase{
    // 获得Documents目录路径
    NSString *documentsPath = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    
    // 文件路径
    NSString *filePath = [documentsPath stringByAppendingPathComponent:@"model.sqlite"];
    
    // 实例化FMDataBase对象
    _db = [FMDatabase databaseWithPath:filePath];
    
    [_db open];
    
//    [self deleteTable:@"audio_files"];
    // 初始化数据表
    NSString *audioFileSql = @"CREATE TABLE  if not exists audio_files (id integer PRIMARY KEY AUTOINCREMENT,audio_file_name  varchar(255) ,date double, is_success boolean)";

    [_db executeUpdate:audioFileSql];
    [_db close];
}

// 删除表
- (BOOL) deleteTable:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"DROP TABLE %@", tableName];
    if (![_db executeUpdate:sqlstr])
    {
        NSLog(@"Delete table error!");
        return NO;
    }
    
    return YES;
}

#pragma mark - 新增上传成功的音频文件记录
- (void)addAudioFile:(AudioFileModel *)audioFileModel {
    
    [_db open];
    
    NSNumber *maxID = @(0);
    
    FMResultSet *res = [_db executeQuery:@"SELECT * FROM audio_files "];
    //获取数据库中最大的ID
    while ([res next]) {
        if ([maxID integerValue] < [[res stringForColumn:@"audio_file_id"] integerValue]) {
            maxID = @([[res stringForColumn:@"audio_file_id"] integerValue] ) ;
        }
        
    }
    maxID = @([maxID integerValue] + 1);
    
    NSTimeInterval datetime=[audioFileModel.date timeIntervalSince1970]*1000;// *1000 是精确到毫秒，不乘就是精确到秒
    NSString *datatimeStr = [NSString stringWithFormat:@"%.0f", datetime];
    NSDate *date1 = [NSDate date];

    [_db executeUpdate:@"INSERT INTO audio_files(audio_file_id,audio_file_name,date,is_success)VALUES(?,?,?,?)",maxID,audioFileModel.audioFileName,datetime,audioFileModel.isSuccess];
   
    [_db close];
}

#pragma mark - 删除上传成功的音频文件记录
- (void)deleteAudioFile:(AudioFileModel *) audioFileModel{
    [_db open];
    
    [_db executeUpdate:@"DELETE FROM audio_files WHERE audio_file_id = ?",audioFileModel.ID];
    
    [_db close];
}

#pragma mark - 更新上传成功的音频文件记录
- (void)updateAudioFile:(AudioFileModel *) audioFileModel{
    [_db open];
    
    [_db executeUpdate:@"UPDATE 'audio_files' SET date = ?,is_success = ? WHERE audio_file_name like  '%%%@%%' ;",audioFileModel.date,audioFileModel.isSuccess,audioFileModel.audioFileName];
     
    [_db close];
}

#pragma mark - 获取音频文件状态
- (NSMutableArray *)getAudioFileByStatus:(AudioFileModel *)audioFileModel {
    [_db open];
    
    NSMutableArray *dataArray = [[NSMutableArray alloc] init];
    
    FMResultSet *res = [_db executeQuery:@"SELECT * FROM audio_files where is_success = ? ",audioFileModel.isSuccess];
    
    NSDateFormatter* dateFormat = [[NSDateFormatter alloc] init];
    [dateFormat setDateFormat:@"yyyy-MM-dd HH:mm:ss"];//设定时间格式,这里可以设置成自己需要的格式
    
    while ([res next]) {
        audioFileModel.ID = @([[res stringForColumn:@"audio_file_id"] integerValue]) ;
        audioFileModel.audioFileName = [res stringForColumn:@"audio_file_name"];
        audioFileModel.date = [dateFormat dateFromString:[res stringForColumn:@"date"]];

        [dataArray addObject:audioFileModel];
    }
    
    [_db close];
    return dataArray;
}


@end
